package com.QK.cnstudy.db;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.QK.cnstudy.entity.Author;
import com.QK.cnstudy.entity.Sentence;
import com.QK.cnstudy.entity.Stat;
import com.QK.cnstudy.entity.Term;
import com.QK.cnstudy.entity.Textbook;
import com.QK.cnstudy.entity.Word;
import com.QK.cnstudy.entity.Work;
import com.QK.cnstudy.entity.WorkRecord;
import com.QK.cnstudy.entity.WrongSubject;

public class DBManager {

    private DBHelper dbHelper;
    private SQLiteDatabase sqLiteDatabase;

    public SQLiteDatabase getSqLiteDatabase() {
        return sqLiteDatabase;
    }

    public void setSqLiteDatabase(SQLiteDatabase sqLiteDatabase) {
        this.sqLiteDatabase = sqLiteDatabase;
    }

    public DBManager(Context context) {
        dbHelper = new DBHelper(context);
        sqLiteDatabase = dbHelper.getWritableDatabase();
    }

    public List<WorkRecord> getWorkRecords() {
        List<WorkRecord> workRecords = new ArrayList<WorkRecord>();
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from work_record order by last_time desc", null);

        while (cursor.moveToNext()) {
            WorkRecord workRecord = new WorkRecord();
            workRecord.setInfoFromCursor(cursor);
            workRecords.add(workRecord);
        }
        cursor.close();
        return workRecords;
    }

    public List<Work> getWorksByTextbookAndTerm(int textbookId, int termId) {
        List<Work> works = new ArrayList<Work>();
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery(
                        "select * from work  where textbook_id = ? and term_id = ? order by last_opentime desc",
                        new String[] { Integer.toString(textbookId), Integer.toString(termId) });

        while (cursor.moveToNext()) {
            Work work = new Work();
            work.setInfoFromCursor(cursor);
            works.add(work);
        }
        cursor.close();
        return works;
    }

    public Work getWorkById(int id) {
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from work where id = ?", new String[] { String.valueOf(id) });
        Work work = new Work();
        if (cursor.moveToNext())
            work.setInfoFromCursor(cursor);

        cursor.close();
        return work;
    }

    public Author getAuthorById(int id) {
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from author where id = ?", new String[] { String.valueOf(id) });
        Author author = new Author();
        if (cursor.moveToNext())
            author.setInfoFromCursor(cursor);

        cursor.close();
        return author;
    }

    public Sentence getSentenceById(int id) {
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from sentence where id = ?", new String[] { String.valueOf(id) });
        Sentence sentence = new Sentence();
        if (cursor.moveToNext())
            sentence.setInfoFromCursor(cursor);

        cursor.close();
        return sentence;
    }

    public Word getWordById(int id) {
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from word where id = ?", new String[] { String.valueOf(id) });
        Word word = new Word();
        if (cursor.moveToNext())
            word.setInfoFromCursor(cursor);

        cursor.close();
        return word;
    }

    public List<WrongSubject> getWrongSubjects() {
        List<WrongSubject> wrongSubjects = new ArrayList<WrongSubject>();
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from wrong_subject", new String[] {});

        while (cursor.moveToNext()) {
            WrongSubject wrongSubject = new WrongSubject();
            wrongSubject.setInfoFromCursor(cursor);
            wrongSubjects.add(wrongSubject);
        }
        cursor.close();
        return wrongSubjects;
    }

    public Author getAuthor(String name) {
        Author author = null;
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from author where work like ?", new String[] { "%" + name + "%" });
        if (cursor.moveToNext()) {
            author = new Author();
            author.setInfoFromCursor(cursor);

        }
        return author;
    }

    public List<Textbook> getTextbooks() {
        List<Textbook> textbooks = new ArrayList<Textbook>();
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from textbook", null);
        while (cursor.moveToNext()) {
            Textbook textbook = new Textbook();
            textbook.setInfoFromCursor(cursor);
            textbooks.add(textbook);
        }
        return textbooks;
    }

    public List<Term> getTerms() {
        List<Term> terms = new ArrayList<Term>();
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from term", null);
        while (cursor.moveToNext()) {
            Term term = new Term();
            term.setInfoFromCursor(cursor);
            terms.add(term);
        }
        return terms;
    }

    public List<Sentence> getSentences(int workId) {
        List<Sentence> sentences = new ArrayList<Sentence>();
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from sentence where work_id = ?",
                        new String[] { Integer.toString(workId) });
        while (cursor.moveToNext()) {
            Sentence sentence = new Sentence();
            sentence.setInfoFromCursor(cursor);
            sentences.add(sentence);
        }
        return sentences;
    }

    public List<Word> getWords(int workId) {
        List<Word> words = new ArrayList<Word>();
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from word where work_id = ?",
                        new String[] { Integer.toString(workId) });
        while (cursor.moveToNext()) {
            Word word = new Word();
            word.setInfoFromCursor(cursor);
            words.add(word);
        }
        return words;
    }

    public int getCurStudyWorkId() {
        Cursor cursor = null;
        cursor = sqLiteDatabase.rawQuery("select * from cur_study_info", null);
        if (cursor.moveToNext()) {
            int tmp;
            if ((tmp = cursor.getColumnIndex("cur_study_work_id")) != -1) {
                return cursor.getInt(tmp);
            }
        }
        cursor.close();
        return -2;
    }

    public void setCurStudyWorkId(int workId) {
        sqLiteDatabase.execSQL("update cur_study_info set cur_study_work_id = ?",
                        new String[] { String.valueOf(workId) });
    }

    public Stat getStat() {
        Cursor cursor = null;
        Stat stat = new Stat();
        cursor = sqLiteDatabase.rawQuery("select * from stat", null);
        if (cursor.moveToNext()) {
            stat.setInfoFromCursor(cursor);
        }
        cursor.close();
        return stat;
    }
}
